package com.cxc.mp.simple.service.impl;

import com.cxc.mp.simple.service.SubmitFinancialReportService;
import com.cxc.mp.simple.util.OracleDbUtil;
import com.cxc.mp.simple.util.OracleJDBCUtil;
import jdk.nashorn.internal.runtime.logging.Logger;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

/**
 * @author cxc
 */
@Service
@Slf4j
public class SubmitFinancialReportServiceImpl implements SubmitFinancialReportService {


    private static final String MERGE_SQL_TEMPLATE =
            "MERGE INTO Middle_report_data a " +
                    "USING (SELECT ? period, ? projectCode, ? value1 FROM dual) b " +
                    "ON (a.period = b.period AND a.projectCode = b.projectCode) " +
                    "WHEN MATCHED THEN UPDATE SET " +
                    "a.money = b.value1, " +
                    "creationtime = sysdate, " +
                    "a.projectName = ?, " +
                    "a.entityName = ? " +
                    "WHEN NOT MATCHED THEN INSERT  " +
                    "VALUES (?, ?, ?, ?, ?, sysdate,sysdate, ?)";

    @Autowired
    private OracleDbUtil oracleDbUtil;

    @Override
    public void submitFinancialReport(Map<String, String> map) {


        Connection conn = null;
        try {
            conn = oracleDbUtil.getConnection();
            processReports(conn, map);
            conn.commit();

        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 异常时回滚事务
                } catch (SQLException ex) {
                   log.error("rollback error", ex);
                }
            }
          log.error("submitFinancialReport error", e);
        } finally {
            OracleJDBCUtil.closeConnection(conn);
        }

    }

    private static void processReports(Connection conn, Map<String, String> map) throws SQLException {
        String nccCode = map.get("ncc_code");
        String accp = map.get("accp");
        String codeEntity = map.get("code_entity");
        String accm = map.get("accm");
        String entityName = map.get("entityname");
        //后续优化，从redis配置中获取，优化频繁查询数据库
        try (PreparedStatement pstmt = conn.prepareStatement(
                "SELECT ncc_code, projectcode, entityname, code_measure, code_bm_meatype, code_bm_trail, code_mvtype FROM report_config WHERE ncc_code = ?")) {
            pstmt.setString(1, nccCode);
            int i=0;
            try (ResultSet rsConfig = pstmt.executeQuery()) {
                while (rsConfig.next()) {
                    String projectCode = rsConfig.getString("projectcode");
                    //相当于MIDDLE_REPORT_DATA中projectname
                    String projectName = rsConfig.getString("entityname");
                    String measure = rsConfig.getString("code_measure");
                    String bmMeaType = rsConfig.getString("code_bm_meatype");
                    String bmTrail = rsConfig.getString("code_bm_trail");
                    String mvType = rsConfig.getString("code_mvtype");
                    //根据report_config 中的维度指标，获取具体指标value
                    double value = getFinancialValue(conn,accp, codeEntity, accm, measure, bmTrail, mvType, bmMeaType);
                    System.out.println(i+++"："+projectCode+":"+value);
                    // 更新Middle_report_data表，采用合并更新插入操作，如果有数据就覆盖，没有数据新插入
                    executeMerge(conn,accp, accm, projectCode, codeEntity,value, projectName, entityName);
                }
            }
        }

    }

    private static void executeMerge(Connection conn, String accp, String accm, String projectCode,String codeEntity ,double value, String projectName, String entityName) {

        try( PreparedStatement pstmt = conn.prepareStatement(MERGE_SQL_TEMPLATE);) {
            // 设置PreparedStatement参数，避免SQL注入
            pstmt.setString(1, accp + "-" + accm);
            pstmt.setString(2, projectCode);
            pstmt.setDouble(3, value);
            pstmt.setString(4, projectName);
            pstmt.setString(5, entityName);
            // 插入时的值
            pstmt.setString(6, accp + "-" + accm);
            pstmt.setString(7, codeEntity);
            pstmt.setString(8, entityName);
            pstmt.setString(9, projectCode);
            pstmt.setString(10, projectName);
            pstmt.setDouble(11, value);
            // 执行更新操作
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Double getFinancialValue(Connection conn, String pkAccp, String codeEntity, String pkAccm,
                                           String codeMeasure, String codeBmMeatype, String mvType, String bmMeaType) throws SQLException {
        // Define the SQL query

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT value FROM tb_cube_60S ")
                .append("WHERE pk_ACCP = ?")
                .append(" AND code_ENTITY = ?")
                .append(" AND pk_ACCM = ?")
                .append(" AND CODE_MEASURE = ?")
                .append(" AND CODE_BM_TRAIL = 'OriginalSingle' ")
                .append(" AND CODE_MVTYPE = 'Actual' ")
                .append(" AND CODE_BM_MEATYPE = ?");

        // Prepare the statement
        try (PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {
            // Bind the input parameters to the statement
            stmt.setString(1, pkAccp);
            stmt.setString(2, codeEntity);
            stmt.setString(3, pkAccm);
            stmt.setString(4, codeMeasure);
            stmt.setString(5, bmMeaType);
            // Execute the query and process the result
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getDouble("value") : 0.0;
            }
        }
    }
}
